package com.api.shopapi.mapper;



import com.api.shopapi.entity.Elderly;
import com.api.shopapi.entity.dto.ElderlyDTO;
import com.api.shopapi.entity.dto.ElderlyDTO1;
import com.api.shopapi.entity.dto.ElderlyDTO2;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Mapper
@Repository
public interface ElderlyMapper {


    @Insert("insert into elderly (" +
            "ElderlyCode, " +
            "UserCode, " +
            "Balance, " +
            "Gender, " +
            "AreaCode, " +
            "AreaName, " +
            "ElderlyType, " +
            "CardNumber, " +
            "Lat, " +
            "Lon, " +
            "SysNation, " +
            "IdCard, " +
            "Birthday, " +
            "Age, " +
            "IsMarriage, " +
            "PoliticsStatus, " +
            "Education, " +
            "CensusRegister, " +
            "Phone, " +
            "HomeAddress, " +
            "LivingStatus, " +
            "heardImg, " +
            "UrgencyContact, " +
            "UrgencyPhone, " +
            "Relation, " +
            "SelfCardAbility, " +
            "PsychologyStatus, " +
            "BloodType, " +
            "DisabilityStatus, " +
            "IrritabilityMedicine, " +
            "MedicalSecurityStatus, " +
            "Illness, " +
            "IsOperation, " +
            "OperationCause, " +
            "OperationTime, " +
            "DietaryHabit, " +
            "Diet, " +
            "SmokeStatus, " +
            "DrinkStatus, " +
            "DrinkKind, " +
            "ExerciseType, " +
            "ExerciseStatus, " +
            "ExerciseTime, " +
            "Pocketbook, " +
            "MonthlyIncome, " +
            "MonthlyConsumption, " +
            "MemberLevel, " +
            "CardInsideCode, " +
            "CardCoding, " +
            "PeriodOfValidity, " +
            "ServiceType, " +
            "ServiceStatus, " +
            "TerminalCode, " +
            "ServiceDemand, " +
            "QrCode, " +
            "Name, " +
            "CardStatus, " +
            "CashPledge, " +
            "Interest, " +
            "Children, " +
            "Subsidy, " +
            "DisabilityLevel, " +
            "ElderlyClass, " +
            "Kinsfolk, " +
            "SocialRelief, " +
            "TotalIncome, " +
            "CreateTime, " +
            "CheckinStatus, " +
            "ZoneCode, " +
            "ZoneName, " +
            "ZoneCodePath," +
            "IcCard," +
            "LastDateClockin, " +
            "InstitutionCode, " +
            "ElderlyClassCode,"+
            "ApplyStatus,"+
            "GovernmentCode " +
            ") " +
            "VALUES " +
            "(" +
            "#{elderly.ElderlyCode} ," +
            "#{elderly.UserCode} ," +
            "#{elderly.Balance} ," +
            "#{elderly.Gender} ," +
            "#{elderly.AreaCode} ," +
            "#{elderly.AreaName} ," +
            "#{elderly.ElderlyType} ," +
            "#{elderly.CardNumber} ," +
            "#{elderly.Lat} ," +
            "#{elderly.Lon} ," +
            "#{elderly.SysNation} ," +
            "#{elderly.IdCard} ," +
            "#{elderly.Birthday} ," +
            "#{elderly.Age} ," +
            "#{elderly.IsMarriage} ," +
            "#{elderly.PoliticsStatus} ," +
            "#{elderly.Education} ," +
            "#{elderly.CensusRegister} ," +
            "#{elderly.Phone} ," +
            "#{elderly.HomeAddress} ," +
            "#{elderly.LivingStatus} ," +
            "#{elderly.HeardImg} ," +
            "#{elderly.UrgencyContact} ," +
            "#{elderly.UrgencyPhone} ," +
            "#{elderly.Relation} ," +
            "#{elderly.SelfCardAbility} ," +
            "#{elderly.PsychologyStatus} ," +
            "#{elderly.BloodType} ," +
            "#{elderly.DisabilityStatus} ," +
            "#{elderly.IrritabilityMedicine} ," +
            "#{elderly.MedicalSecurityStatus} ," +
            "#{elderly.Illness} ," +
            "#{elderly.IsOperation} ," +
            "#{elderly.OperationCause} ," +
            "#{elderly.OperationTime} ," +
            "#{elderly.DietaryHabit} ," +
            "#{elderly.Diet} ," +
            "#{elderly.SmokeStatus} ," +
            "#{elderly.DrinkStatus} ," +
            "#{elderly.DrinkKind} ," +
            "#{elderly.ExerciseType} ," +
            "#{elderly.ExerciseStatus} ," +
            "#{elderly.ExerciseTime} ," +
            "#{elderly.Pocketbook} ," +
            "#{elderly.MonthlyIncome} ," +
            "#{elderly.MonthlyConsumption} ," +
            "#{elderly.MemberLevel} ," +
            "#{elderly.CardInsideCode} ," +
            "#{elderly.CardCoding} ," +
            "#{elderly.PeriodOfValidity}," +
            "#{elderly.ServiceType} ," +
            "#{elderly.ServiceStatus} ," +
            "#{elderly.TerminalCode} ," +
            "#{elderly.ServiceDemand} ," +
            "#{elderly.QrCode} ," +
            "#{elderly.Name} ," +
            "#{elderly.CardStatus} ," +
            "#{elderly.CashPledge} ," +
            "#{elderly.Interest} ," +
            "#{elderly.Children} ," +
            "#{elderly.Subsidy} ," +
            "#{elderly.DisabilityLevel} ," +
            "#{elderly.ElderlyClass} ," +
            "#{elderly.Kinsfolk} ," +
            "#{elderly.SocialRelief} ," +
            "#{elderly.TotalIncome} ," +
            "#{elderly.CreateTime} ," +
            "#{elderly.CheckinStatus}, " +
            "#{elderly.ZoneCode}, " +
            "#{elderly.ZoneName}, " +
            "#{elderly.ZoneCodePath}, " +
            "#{elderly.IcCard}," +
            "#{elderly.LastDateClockin}, " +
            "#{elderly.institutionCode}, " +
            "#{elderly.elderlyClassCode}, " +
            "#{elderly.ApplyStatus}," +
            "#{elderly.GovernmentCode} " +
            ") ")
    int insert(@Param("elderly") Elderly elderly);

    @Insert("<script>" +
            "insert into elderly (ElderlyCode,UserCode,Balance,Gender,Phone,Name,CheckinStatus" +
            ",IdCard,Age,HomeAddress,Birthday,CreateTime,ElderlyClass,ZoneCode,ZoneName," +
            "ZoneCodePath,GovernmentCode) values " +

            "<foreach collection='list' item='obj' separator=','>" +
            "(#{obj.ElderlyCode},#{obj.UserCode},#{obj.Balance},#{obj.Gender},#{obj.Phone}," +
            "#{obj.Name},#{obj.CheckinStatus}" +
            ",#{obj.IdCard},#{obj.Age},#{obj.HomeAddress},#{obj.Birthday}," +
            "#{obj.CreateTime},#{obj.ElderlyClass},#{obj.ZoneCode}," +
            "#{obj.ZoneName},#{obj.ZoneCodePath},#{obj.GovernmentCode})" +
            "</foreach>" +

            "</script>")
    Integer batchInsert(List<Elderly> list);

    @Insert("<script>" +
            "insert into elderly (ElderlyCode,UserCode,Balance,Gender,Phone,Name,CheckinStatus" +
            ",IdCard,Age,HomeAddress,Birthday,CreateTime,UrgencyContact,UrgencyPhone," +
            "GovernmentCode) values " +

            "<foreach collection='list' item='obj' separator=','>" +
            "(#{obj.ElderlyCode},#{obj.UserCode},#{obj.Balance},#{obj.Gender},#{obj.Phone}," +
            "#{obj.Name},#{obj.CheckinStatus}" +
            ",#{obj.IdCard},#{obj.Age},#{obj.HomeAddress},#{obj.Birthday}," +
            "#{obj.CreateTime},#{obj.UrgencyContact}," +
            "#{obj.UrgencyPhone},#{obj.GovernmentCode})" +
            "</foreach>" +

            "</script>")
    Integer batchInsert2(List<Elderly> list);



    @Delete("delete from elderly where elderlyCode=#{elderlyCode} ")
    int delete(@Param("elderlyCode") String elderlyCode);

    @Update("update elderly " +
            "set " +
            "Balance=#{elderly.Balance} ," +
            "AreaCode=#{elderly.AreaCode} ," +
            "AreaName=#{elderly.AreaName} ," +
            "ElderlyType=#{elderly.ElderlyType} ," +
            "Lat=#{elderly.Lat} ," +
            "Lon=#{elderly.Lon} ," +
            "IdCard=#{elderly.IdCard} ," +
            "Phone=#{elderly.Phone} ," +
            "HomeAddress=#{elderly.HomeAddress} ," +
            "HeardImg=#{elderly.HeardImg} ," +
            "Name=#{elderly.Name} ," +
            "CreateTime=#{elderly.CreateTime} ," +
            "CheckinStatus=#{elderly.CheckinStatus}, " +
            "RejectMessage=#{elderly.RejectMessage}, " +
            "ZoneCode=#{elderly.ZoneCode} ," +
            "ZoneName=#{elderly.ZoneName}, " +
            "ZoneCodePath=#{elderly.ZoneCodePath}, " +
            "institutionCode=#{elderly.institutionCode}, " +
            "elderlyClassCode=#{elderly.elderlyClassCode}, " +
            "applyStatus=#{elderly.applyStatus}, " +
            "AssessSpanDay=#{elderly.AssessSpanDay}, " +
            "GovernmentCode=#{elderly.GovernmentCode} " +
            "where " +
            "ElderlyCode=#{elderly.ElderlyCode}  ")
    int update(@Param("elderly") Elderly elderly);


    @Select("select  * from elderly where  ElderlyCode=#{elderlyCode} limit 1")
    ElderlyDTO getByElderlyCode(@Param("elderlyCode") String elderlyCode);

    @Select("select  * from  elderly where UserCode=#{userCode} limit 1 ")
    ElderlyDTO getByUserCode(@Param("userCode") String userCode);

    @Select("<script>" +
            "select IdCard from elderly where IdCard in" +
            "<foreach collection='list' item='item' open='(' separator=',' close=')' >" +
            "#{item.IdCard}"+
            "</foreach>" +
            "</script>")
    List<String> batchSelectPhone(List<Elderly> list);

    @Select("<script>" +
            "select IdCard from elderly where IdCard in" +
            "<foreach collection='list' item='item' open='(' separator=',' close=')' >" +
            "#{item.IdCard}"+
            "</foreach>" +
            "</script>")
    List<String> batchSelectIdCard(List<Elderly> list);

    @Select("<script>" +
            "select u.GovernmentCode,e.*,u.Name userName, u.Phone userPhone ,u.Status userStatus, u.CreateTime from  elderly as e,user u " +
            "where u.GovernmentCode=#{governmentCode}  " +
            "and e.UserCode=u.UserCode " +

            "<if test ='name !=null and name!=\"\" '>" +
            " and e.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test ='status !=-1 '>" +
            "and u.Status =#{status} " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '>" +
            " and u.Phone = #{phone} " +
            "</if> " +

            " order by  u.CreateTime  desc " +
            "</script>")
    List<ElderlyDTO> getListByGovernmentCode(@Param("governmentCode") String governmentCode,
                                             @Param("name") String name,
                                             @Param("phone") String phone, @Param("status") int status);



    @Select("select  * from  elderly where Phone=#{phone} limit 1 ")
    ElderlyDTO getByPhone(@Param("phone") String phone);

    @Select("select * from elderly where IdCard=#{idCard} limit 1 ")
    ElderlyDTO getByIdCard(@Param("idCard") String idCard);


    //政府查看服务对象列表 模糊查询
    @Select("<script>" +
            "select e.* from " +
            " elderly e join user u " +
            " on e.UserCode = u.UserCode " +
            " where " +
            " (e.TimeAudit between #{startTime} and #{endTime}) and u.GovernmentCode=#{governmentCode} and e.StatusAudit = 1 and e.IsCut=0  " +
            " <if test =' zoneCode !=\"00000000-0000-0000-0000-000000000000\" '> " +
            " and e.ZoneCodePath like concat('%',#{zoneCode},'%') " +
            " </if>" +
            " <if test ='phone !=\"\" '> " +
            "  and e.Phone like concat('%',#{phone},'%') " +
            " </if> " +
            " <if test =' idCard !=\"\" '> " +
            " and  e.IdCard like concat('%',#{idCard},'%')  " +
            " </if> " +
            " <if test =' name !=\"\" '> " +
            " and  u.Name like concat('%',#{name},'%')  " +
            " </if> " +
            " <if test =' censusRegister !=-1 '> " +
            " and  e.CensusRegister=#{censusRegister}   " +
            " </if> " +
            " order by  e.TimeAudit desc " +
            "</script>")
    List<ElderlyDTO> getSelectForGov(@Param("startTime") Date startTime,
                                     @Param("endTime") Date endTime,
                                     @Param("zoneCode") String zoneCode,
                                     @Param("governmentCode") String governmentCode,
                                     @Param("phone") String phone,
                                     @Param("idCard") String idCard,
                                     @Param("name") String name,
                                     @Param("censusRegister") int censusRegister);

    @Select("<script>" +
            "select e.* from elderly e left join user u on e.UserCode = u.UserCode " +
            " where u.GovernmentCode=#{governmentCode}" +
            "<if test =' zoneCode !=\"00000000-0000-0000-0000-000000000000\" '>" +
            " and e.ZoneCodePath like concat('%',#{zoneCode},'%')" +
            "</if>" +
            "<if test ='phone !=\"\" and phone !=null '>" +
            " and u.Phone like concat('%',#{phone},'%')" +
            "</if>" +
            "<if test ='name !=\"\" and test !=null '>" +
            " and e.Name like concat('%',#{name},'%')" +
            "</if>" +
            "<if test ='censusRegister !=-1 '>" +
            " and e.CensusRegister=#{censusRegister}" +
            "</if>" +
            "<if test ='elderlyClass !=-1 '>" +
            " and e.ElderlyClass=#{elderlyClass}" +
            "</if>" +
            "</script>")
    List<ElderlyDTO> getFiltrateBatchRechargeElderly(
            @Param("zoneCode") String zoneCode,
            @Param("phone") String phone,
            @Param("governmentCode") String governmentCode,
            @Param("name") String name,
            @Param("censusRegister") int censusRegister,
            @Param("elderlyClass") int elderlyClass);

    @Select("<script>" +
            "select e.* from elderly e left join user u on e.UserCode = u.UserCode " +
            " where u.GovernmentCode=#{governmentCode}" +
            "<if test =' zoneCode !=\"00000000-0000-0000-0000-000000000000\" '>" +
            " and e.ZoneCode in (select ZoneCode from zone where " +
            " ParentZoneCode=#{zoneCode} or " +
            " ZoneCode=#{zoneCode})" +
            "</if>" +
            "<if test ='phone !=\"\" and phone !=null  '>" +
            " and e.Phone = #{phone}" +
            "</if>" +
            "<if test ='name !=\"\" and name !=null '>" +
            " and e.Name = #{name}" +
            "</if>" +
            "<if test ='censusRegister !=-1 '>" +
            " and e.CensusRegister=#{censusRegister}" +
            "</if>" +
            "<if test ='elderlyClass !=-1 '>" +
            " and e.ElderlyClass=#{elderlyClass}" +
            "</if>" +
            "</script>")
    List<ElderlyDTO> getRechargeElderly(
            @Param("zoneCode") String zoneCode,
            @Param("phone") String phone,
            @Param("governmentCode") String governmentCode,
            @Param("name") String name,
            @Param("censusRegister") int censusRegister,
            @Param("elderlyClass") int elderlyClass);


    @Select("<script>" +
            "select e.*,u.Name userName, u.Phone userPhone ,u.Status userStatus, u.CreateTime,u.GovernmentCode from elderly e left join user u on e.UserCode = u.UserCode " +
            " where u.GovernmentCode=#{governmentCode}" +
            "<if test ='name !=\"\" and name !=null '>" +
            " and e.Name like concat('%',#{name},'%') " +
            "</if>" +
            "and u.Status = 1 " +
            "and Month(e.Birthday) =#{month} " +
            "and DAY(e.Birthday) >= #{day} " +
            "order by DAY(e.Birthday) Asc" +
            "</script>")
    List<ElderlyDTO> getCarePage(
            @Param("governmentCode") String governmentCode,
            @Param("name") String name,
            @Param("month") int month,
            @Param("day") int day);


    @Select("select sum(Price) from  order_business " +
            "where ElderlyCode=#{elderlyCode} " +
            "and Status in (14,16,3,7) " +
            "and (CreateTime BETWEEN #{startTime} and #{endTime})   ")
    Double getMonetary(@Param("elderlyCode") String elderlyCode,
                       @Param("startTime") Date startTime,
                       @Param("endTime") Date endTime);


    @Select("<script>" +
            "select e.*,x.TotalPrice,x.OrderCount from elderly e  left join " +
            "(select o.UserCode,SUM(ob.Price) as TotalPrice,Count(o.OrderCode)as OrderCount from order_business ob left join `order` o on ob.OrderCode = o.OrderCode " +
            "where o.GovernmentCode=#{governmentCode} " +
            "and ob.CreateTime between #{startTime} and #{endTime} " +
            "and ob.Status in(3,7,14,16) " +
            "<if test ='businessCode !=\"\" and businessCode !=null '>" +
            " and ob.BusinessCode =#{businessCode} " +
            "</if>" +
            "<if test ='orderType !=-1 '>" +
            " and ob.OrderType=#{orderType}" +
            "</if>" +
            "GROUP BY o.UserCode) x " +
            "on e.UserCode = x.UserCode " +
            "where e.UserCode=x.UserCode " +
            "<if test ='name !=\"\" and name !=null '>" +
            " and e.Name =#{name} " +
            "</if>" +
            "</script>")
    List<ElderlyDTO> getForConsumerUsers(@Param("governmentCode") String governmentCode,
                                         @Param("businessCode") String businessCode,
                                         @Param("startTime")String startTime,
                                         @Param("endTime")String endTime,
                                         @Param("orderType")int orderType,
                                         @Param("name")String name);


    @Select("select count(ElderlyCode) from elderly e left join user u on e.UserCode=u.UserCode " +
            "where e.ElderlyClass =#{elderlyClassValue} and u.GovernmentCode=#{governmentCode} and u.status=1")
    int getElderlyClassCount(@Param("elderlyClassValue") int elderlyClassValue,@Param("governmentCode") String governmentCode);

    @Select("select count(ElderlyCode) from elderly e left join user u on e.UserCode=u.UserCode " +
            "where e.ElderlyClass =#{elderlyClassValue} and u.GovernmentCode=#{governmentCode}")
    int getElderlyClassCountTotal(@Param("elderlyClassValue") int elderlyClassValue,@Param("governmentCode") String governmentCode);


    @Select("select count(ElderlyCode) from elderly e left join user u on e.UserCode=u.UserCode " +
            "where e.ElderlyClass =#{elderlyClassValue} and u.GovernmentCode=#{governmentCode} and u.status=1 and DATE_FORMAT( e.CreateTime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    int getElderlyClassCountByNew(@Param("elderlyClassValue") int elderlyClassValue,@Param("governmentCode") String governmentCode);


    @Select("select * from elderly e left join card_status cs on e.IcCard=cs.CardNumber " +
            "where IcCard=#{icCard} and cs.Status=1 limit 1")
    Elderly getByIcCard(@Param("icCard") String icCard);


    @Select("select * from elderly e where IcCard=#{icCard} limit 1")
    Elderly getByIcCardAllStatus(@Param("icCard") String icCard);

    //服务端查询老人
    @Select("<script>" +
            "select * from elderly" +
            " where" +
            " GovernmentCode=#{governmentCode}" +
            " and Status != 2 " +
            "<if test ='name !=null and name!=\"\" '> " +
            " and Name=#{name} " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '> " +
            " and Phone=#{phone} " +
            "</if>" +
            "<if test =' zoneCode !=\"00000000-0000-0000-0000-000000000000\" '>" +
            " and instr(ZoneCodePath,#{zoneCode})>0 " +
            "</if>" +
            "</script>")
    List<ElderlyDTO> getElderlyDTOByAppNew(@Param("zoneCode") String zoneCode,
                                           @Param("governmentCode") String governmentCode,
                                           @Param("name") String name,
                                           @Param("phone") String phone);

    @Select("SELECT e.*,z.Name ZoneCodePathName from elderly e left join zone z on e.zoneCode=z.zoneCode " +
            "where userCode=#{userCode} ")
    ElderlyDTO2 getElderlyList(@Param("userCode") String userCode);

    @Select("<script>" +
            "select * from elderly where Status = 1 " +
            "and GovernmentCode =#{governmentCode} and (CreateTime between #{startTime} and #{endTime}) " +
            "and ElderlyCode in (select ElderlyCode from recharge where (CreateTime between #{startTime} and #{endTime}) and AddBalance &gt; 0) " +
            "<if test ='name !=null and name!=\"\" '> " +
            " and Name=#{name} " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '> " +
            " and Phone=#{phone} " +
            "</if>" +"<if test ='idCard !=null and  idCard!=\"\" '> " +
            " and IdCard=#{idCard} " +
            "</if>" +
            "<if test =' zoneCode !=\"00000000-0000-0000-0000-000000000000\" '>" +
            " and instr(ZoneCodePath,#{zoneCode})>0 " +
            "</if>" +
            "</script>")
    List<ElderlyDTO> getNewElderly(@Param("name")String name,
                                   @Param("idCard") String idCard,
                                   @Param("phone") String phone,
                                   @Param("elderlyClass") int elderlyClass,
                                   @Param("zoneCode") String zoneCode,
                                   @Param("governmentCode") String governmentCode,
                                   @Param("startTime") Date startTime,
                                   @Param("endTime") Date endTime);



    //查询政府下的老人
    @Select("select ElderlyCode from elderly where GovernmentCode=#{governmentCode} ")
    List<Elderly> ListElderlyByGovernmentCode(@Param("governmentCode") String governmentCode);


    //查询政府下到男性老人
    @Select("select ElderlyCode from elderly where GovernmentCode=#{governmentCode} and Gender=1 ")
    List<Elderly> ListElderlyByGovernmentCodeMan(@Param("governmentCode") String governmentCode);



    @Select("<script>" +
            "select e.*,u.Name userName, u.Phone userPhone ,u.Status userStatus,u.loginName loginName" +
            " from elderly e join user u on e.UserCode=u.UserCode" +
            " where " +
            " e.institutionCode=#{institutionCode} and e.applyStatus=4" +

            "<if test ='name !=null and name!=\"\" '>" +
            " and e.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test ='status !=-1 '>" +
            " and u.Status =#{status} " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '>" +
            " and e.Phone = #{phone} " +
            "</if> " +

//            "<if test ='zoneCode !=null and  zoneCode!=\"\" '>" +
//            " and instr(e.ZoneCodePath,#{zoneCode})>0" +
//            "</if>" +

            " order by e.CreateTime desc " +
            "</script>")
    List<ElderlyDTO> listByInsCode(@Param("institutionCode") String institutionCode,
                                   @Param("name") String name,
                                   @Param("phone") String phone,
                                   @Param("status") int status,
                                   @Param("zoneCode")String zoneCode);

    @Select("<script>" +
            "select elderlyCode from elderly " +
            " where " +
            " institutionCode=#{institutionCode}" +
            "</script>")
    List<Elderly> listByInsCode2(@Param("institutionCode") String institutionCode);

    @Select("<script>" +
            "select elderlyCode from elderly" +
            " where institutionCode=#{institutionCode}" +
            " and applyStatus=4" +
            " and checkinStatus =#{checkinStatus} " +

            "<if test ='name !=null and name!=\"\" '>" +
            " and Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '>" +
            " and Phone = #{phone} " +
            "</if> " +

            " order by CreateTime desc " +
            "</script>")
    List<Elderly> listByInsCodeCheckinStatus(@Param("institutionCode") String institutionCode,
                                             @Param("name") String name,
                                             @Param("phone") String phone,
                                             @Param("checkinStatus") int checkinStatus);

    @Select("<script>" +
            "select e.*,u.Name userName, u.Phone userPhone ,u.Status userStatus,u.loginName loginName" +
            " from elderly e join user u on e.UserCode=u.UserCode " +
            " where e.governmentCode=#{governmentCode} and e.applyStatus=4 " +

            "<if test ='name !=null and name!=\"\" '>" +
            " and e.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test ='phone !=null and phone!=\"\" '>" +
            " and e.Phone = #{phone} " +
            "</if> " +

//            "<if test ='zoneCode !=null and  zoneCode!=\"\" '>" +
//            " and instr(e.ZoneCodePath,#{zoneCode})>0" +
//            "</if>" +

            " order by e.CreateTime desc " +
            "</script>")
    List<ElderlyDTO> listByGovCode(@Param("governmentCode") String governmentCode,
                                   @Param("name") String name, @Param("phone") String phone,
                                   @Param("status") int status,
                                   @Param("zoneCode")String zoneCode);


    @Select("<script>" +
            "select elderlyCode from elderly " +
            " where governmentCode=#{governmentCode}" +
            " and applyStatus=4 " +
            "<if test ='institutionCode !=null and institutionCode!=\"\" '>" +
            " and institutionCode = #{institutionCode} " +
            "</if>" +
            "<if test ='name !=null and name!=\"\" '>" +
            " and Name like concat('%',#{name},'%') " +
            "</if>" +
            " order by CreateTime desc " +
            "</script>")
    List<Elderly> listByGovCodeInsCodeName(@Param("governmentCode") String governmentCode,
                                           @Param("institutionCode") String institutionCode,
                                           @Param("name") String name);


    @Select("<script>" +
            "select elderlyCode from elderly" +
            " where governmentCode=#{governmentCode}" +
            " and checkinStatus =#{checkinStatus} " +

            "<if test ='name !=null and name!=\"\" '>" +
            " and Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '>" +
            " and Phone = #{phone} " +
            "</if> " +

            " order by CreateTime desc " +
            "</script>")
    List<Elderly> listByGovCodeCheckinStatus(@Param("governmentCode") String governmentCode,
                                             @Param("name") String name,
                                             @Param("phone") String phone,
                                             @Param("checkinStatus") int checkinStatus);



    @Select("<script>" +
            "select elderlyCode from elderly" +
            " where institutionCode=#{institutionCode}" +
            " and applyStatus =#{applyStatus} " +

            "<if test ='name !=null and name!=\"\" '>" +
            " and Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '>" +
            " and Phone = #{phone} " +
            "</if> " +

            " order by CreateTime desc " +
            "</script>")
    List<Elderly> listByInsCodeApplyStatus(@Param("institutionCode") String institutionCode,
                                             @Param("name") String name,
                                             @Param("phone") String phone,
                                             @Param("applyStatus") int applyStatus);

    @Select("<script>" +
            "select elderlyCode from elderly" +
            " where governmentCode=#{governmentCode}" +
            " and applyStatus =#{applyStatus} " +

            "<if test ='name !=null and name!=\"\" '>" +
            " and Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '>" +
            " and Phone = #{phone} " +
            "</if> " +

            " order by CreateTime desc " +
            "</script>")
    List<Elderly> listByGovCodeApplyStatus(@Param("governmentCode") String governmentCode,
                                           @Param("name") String name,
                                           @Param("phone") String phone,
                                           @Param("applyStatus") int applyStatus);



}
